package oracle

import (
	"context"
	"database/sql"
	"fmt"
	"slices"
	"strconv"
	"strings"
	"testing"
	"time"

	"github.com/docker/docker/api/types/container"
	"github.com/google/go-cmp/cmp"
	"github.com/pkg/errors"
	"github.com/stretchr/testify/require"
	"github.com/testcontainers/testcontainers-go"
	"github.com/testcontainers/testcontainers-go/wait"
	"google.golang.org/protobuf/testing/protocmp"

	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	"github.com/bytebase/bytebase/backend/plugin/db"
	oracledb "github.com/bytebase/bytebase/backend/plugin/db/oracle"
	plsqlparser "github.com/bytebase/bytebase/backend/plugin/parser/plsql"
	"github.com/bytebase/bytebase/backend/plugin/schema"
	"github.com/bytebase/bytebase/backend/store/model"
)

// TestGenerateMigrationWithTestcontainer tests the generate migration function
// by applying migrations and rollback to verify the schema can be restored.
func TestGenerateMigrationWithTestcontainer(t *testing.T) {
	if testing.Short() {
		t.Skip("Skipping Oracle testcontainer test in short mode")
	}

	ctx := context.Background()

	// Use Oracle Free image (Oracle 23c Free) slim version
	imageName := "gvenzl/oracle-free:slim"

	// Start Oracle container
	req := testcontainers.ContainerRequest{
		Image: imageName,
		Env: map[string]string{
			"ORACLE_PASSWORD":   "test123",
			"APP_USER":          "testuser",
			"APP_USER_PASSWORD": "testpass",
		},
		ExposedPorts: []string{"1521/tcp"},
		WaitingFor: wait.ForLog("DATABASE IS READY TO USE!").
			WithStartupTimeout(10 * time.Minute),
		HostConfigModifier: func(hc *container.HostConfig) {
			hc.ShmSize = 1 * 1024 * 1024 * 1024 // 1GB shared memory
		},
	}

	container, err := testcontainers.GenericContainer(ctx, testcontainers.GenericContainerRequest{
		ContainerRequest: req,
		Started:          true,
	})
	if err != nil {
		// Get detailed logs for debugging
		if container != nil {
			logs, logErr := container.Logs(ctx)
			if logErr == nil {
				buf := make([]byte, 10000)
				n, _ := logs.Read(buf)
				t.Logf("Container logs on failure: %s", string(buf[:n]))
			}
		}
		require.NoError(t, err, "Failed to start Oracle container")
	}
	defer func() {
		if err := container.Terminate(ctx); err != nil {
			t.Logf("failed to terminate container: %s", err)
		}
	}()

	// Get connection details
	host, err := container.Host(ctx)
	require.NoError(t, err)
	port, err := container.MappedPort(ctx, "1521")
	require.NoError(t, err)

	// Test cases with various schema changes
	testCases := []struct {
		name          string
		initialSchema string
		migrationDDL  string
		description   string
	}{
		{
			name: "create_tables_with_fk",
			initialSchema: `
CREATE TABLE USERS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    USERNAME VARCHAR2(50) NOT NULL,
    EMAIL VARCHAR2(100) NOT NULL,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX UK_EMAIL ON USERS(EMAIL);
CREATE INDEX IDX_USERNAME ON USERS(USERNAME);

CREATE TABLE POSTS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    USER_ID NUMBER NOT NULL,
    TITLE VARCHAR2(200) NOT NULL,
    CONTENT CLOB,
    PUBLISHED_AT TIMESTAMP,
    CONSTRAINT FK_USER FOREIGN KEY (USER_ID) REFERENCES USERS(ID) ON DELETE CASCADE
);

CREATE INDEX IDX_USER_ID ON POSTS(USER_ID);
`,
			migrationDDL: `
DROP TABLE POSTS CASCADE CONSTRAINTS;
DROP TABLE USERS CASCADE CONSTRAINTS;`,
			description: "Create tables with foreign key constraints",
		},
		{
			name: "basic_table_operations",
			initialSchema: `
CREATE TABLE USERS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    USERNAME VARCHAR2(50) NOT NULL,
    EMAIL VARCHAR2(100) NOT NULL,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE POSTS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    USER_ID NUMBER NOT NULL,
    TITLE VARCHAR2(200) NOT NULL,
    CONTENT CLOB,
    CONSTRAINT FK_USER FOREIGN KEY (USER_ID) REFERENCES USERS(ID)
);

CREATE INDEX IDX_POSTS_USER_ID ON POSTS(USER_ID);
`,
			migrationDDL: `
-- Add new column
ALTER TABLE USERS ADD IS_ACTIVE NUMBER(1) DEFAULT 1;

-- Create new table
CREATE TABLE COMMENTS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    POST_ID NUMBER NOT NULL,
    USER_ID NUMBER NOT NULL,
    CONTENT CLOB NOT NULL,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT FK_POST FOREIGN KEY (POST_ID) REFERENCES POSTS(ID),
    CONSTRAINT FK_COMMENT_USER FOREIGN KEY (USER_ID) REFERENCES USERS(ID)
);

-- Add new index
CREATE INDEX IDX_USERS_EMAIL ON USERS(EMAIL);

-- Add check constraint
ALTER TABLE POSTS ADD CONSTRAINT CHECK_TITLE_LENGTH CHECK (LENGTH(TITLE) > 0);
`,
			description: "Basic table operations with columns, constraints, and indexes",
		},
		{
			name: "views_and_functions",
			initialSchema: `
CREATE TABLE PRODUCTS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    PRICE NUMBER(10, 2) NOT NULL,
    STOCK NUMBER DEFAULT 0
);

CREATE TABLE ORDERS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    PRODUCT_ID NUMBER NOT NULL,
    QUANTITY NUMBER NOT NULL,
    TOTAL NUMBER(10, 2),
    CONSTRAINT FK_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(ID)
);
`,
			migrationDDL: `
-- Create view
CREATE VIEW PRODUCT_INVENTORY AS
SELECT 
    P.ID,
    P.NAME,
    P.PRICE,
    P.STOCK,
    NVL(SUM(O.QUANTITY), 0) AS TOTAL_ORDERED
FROM PRODUCTS P
LEFT JOIN ORDERS O ON P.ID = O.PRODUCT_ID
GROUP BY P.ID, P.NAME, P.PRICE, P.STOCK;

-- Create function
CREATE OR REPLACE FUNCTION CALCULATE_ORDER_TOTAL(P_PRODUCT_ID NUMBER, P_QUANTITY NUMBER)
RETURN NUMBER
IS
    V_PRICE NUMBER(10, 2);
BEGIN
    SELECT PRICE INTO V_PRICE FROM PRODUCTS WHERE ID = P_PRODUCT_ID;
    RETURN P_QUANTITY * V_PRICE;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 0;
END;
/

-- Create materialized view
CREATE MATERIALIZED VIEW PRODUCT_STATS AS
SELECT 
    PRODUCT_ID,
    COUNT(*) AS ORDER_COUNT,
    SUM(QUANTITY) AS TOTAL_QUANTITY,
    SUM(TOTAL) AS TOTAL_REVENUE
FROM ORDERS
GROUP BY PRODUCT_ID;

-- Create index on materialized view
CREATE INDEX IDX_PRODUCT_STATS_REVENUE ON PRODUCT_STATS(TOTAL_REVENUE DESC);
`,
			description: "Views, functions, and materialized views",
		},
		{
			name: "sequences",
			initialSchema: `
CREATE TABLE ITEMS (
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL
);
`,
			migrationDDL: `
-- Create sequence
CREATE SEQUENCE ORDER_SEQ START WITH 1000 INCREMENT BY 10;

-- Create table using sequence
CREATE TABLE SALES_ORDERS (
    ID NUMBER DEFAULT ORDER_SEQ.NEXTVAL PRIMARY KEY,
    ITEM_ID NUMBER NOT NULL,
    QUANTITY NUMBER NOT NULL,
    CONSTRAINT FK_ITEM FOREIGN KEY (ITEM_ID) REFERENCES ITEMS(ID)
);
`,
			description: "Sequences and their usage in tables",
		},
		{
			name: "drop_indexes_and_constraints",
			initialSchema: `
CREATE TABLE PRODUCTS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    CATEGORY VARCHAR2(50),
    PRICE NUMBER(10, 2),
    SUPPLIER_ID NUMBER
);

CREATE INDEX IDX_PRODUCTS_CATEGORY ON PRODUCTS(CATEGORY);
CREATE INDEX IDX_PRODUCTS_PRICE ON PRODUCTS(PRICE);
CREATE UNIQUE INDEX IDX_PRODUCTS_NAME ON PRODUCTS(NAME);

ALTER TABLE PRODUCTS ADD CONSTRAINT CHECK_PRICE_POSITIVE CHECK (PRICE > 0);
ALTER TABLE PRODUCTS ADD CONSTRAINT CHECK_NAME_LENGTH CHECK (LENGTH(NAME) >= 3);
`,
			migrationDDL: `
-- Drop indexes and constraints
DROP INDEX IDX_PRODUCTS_CATEGORY;
DROP INDEX IDX_PRODUCTS_PRICE;
DROP INDEX IDX_PRODUCTS_NAME;
ALTER TABLE PRODUCTS DROP CONSTRAINT CHECK_PRICE_POSITIVE;
ALTER TABLE PRODUCTS DROP CONSTRAINT CHECK_NAME_LENGTH;
`,
			description: "Drop indexes and constraints from tables",
		},
		{
			name: "drop_views_and_functions",
			initialSchema: `
CREATE TABLE SALES (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    PRODUCT_NAME VARCHAR2(100) NOT NULL,
    SALE_AMOUNT NUMBER(10, 2) NOT NULL,
    SALE_DATE DATE NOT NULL
);

CREATE VIEW MONTHLY_SALES AS
SELECT 
    EXTRACT(YEAR FROM SALE_DATE) AS YEAR_VAL,
    EXTRACT(MONTH FROM SALE_DATE) AS MONTH_VAL,
    SUM(SALE_AMOUNT) AS TOTAL_SALES
FROM SALES
GROUP BY EXTRACT(YEAR FROM SALE_DATE), EXTRACT(MONTH FROM SALE_DATE);

CREATE MATERIALIZED VIEW TOP_PRODUCTS AS
SELECT 
    PRODUCT_NAME,
    COUNT(*) AS SALE_COUNT,
    SUM(SALE_AMOUNT) AS TOTAL_REVENUE
FROM SALES
GROUP BY PRODUCT_NAME
ORDER BY SUM(SALE_AMOUNT) DESC;

CREATE OR REPLACE FUNCTION GET_MONTHLY_TOTAL(YEAR_PARAM NUMBER, MONTH_PARAM NUMBER)
RETURN NUMBER
IS
    V_TOTAL NUMBER;
BEGIN
    SELECT NVL(SUM(SALE_AMOUNT), 0) INTO V_TOTAL
    FROM SALES
    WHERE EXTRACT(YEAR FROM SALE_DATE) = YEAR_PARAM
    AND EXTRACT(MONTH FROM SALE_DATE) = MONTH_PARAM;
    
    RETURN V_TOTAL;
END;
/

CREATE OR REPLACE FUNCTION CALCULATE_DISCOUNT(AMOUNT NUMBER)
RETURN NUMBER
IS
BEGIN
    RETURN AMOUNT * 0.1;
END;
/
`,
			migrationDDL: `
-- Drop views and functions
DROP MATERIALIZED VIEW TOP_PRODUCTS;
DROP VIEW MONTHLY_SALES;
DROP FUNCTION GET_MONTHLY_TOTAL;
DROP FUNCTION CALCULATE_DISCOUNT;
`,
			description: "Drop views and functions that depend on tables",
		},
		{
			name: "alter_table_columns",
			initialSchema: `
CREATE TABLE PRODUCTS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(50) NOT NULL,
    PRICE NUMBER(8, 2) NOT NULL,
    DESCRIPTION CLOB,
    CATEGORY VARCHAR2(30),
    IS_ACTIVE NUMBER(1) DEFAULT 1
);

CREATE INDEX IDX_PRODUCTS_CATEGORY ON PRODUCTS(CATEGORY);
CREATE INDEX IDX_PRODUCTS_PRICE ON PRODUCTS(PRICE);
`,
			migrationDDL: `
-- Alter table operations
ALTER TABLE PRODUCTS ADD CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE PRODUCTS ADD STOCK_QUANTITY NUMBER DEFAULT 0;
ALTER TABLE PRODUCTS ADD WEIGHT NUMBER(5, 2);

-- Change column types and constraints
ALTER TABLE PRODUCTS MODIFY NAME VARCHAR2(100);
ALTER TABLE PRODUCTS MODIFY PRICE NUMBER(10, 2);
ALTER TABLE PRODUCTS MODIFY DESCRIPTION NOT NULL;

-- Add constraints
ALTER TABLE PRODUCTS ADD CONSTRAINT CHECK_PRICE_POSITIVE CHECK (PRICE > 0);
ALTER TABLE PRODUCTS ADD CONSTRAINT CHECK_STOCK_NON_NEGATIVE CHECK (STOCK_QUANTITY >= 0);

-- Add new index
CREATE INDEX IDX_PRODUCTS_CREATED_AT ON PRODUCTS(CREATED_AT);
CREATE UNIQUE INDEX IDX_PRODUCTS_NAME_CATEGORY ON PRODUCTS(NAME, CATEGORY);
`,
			description: "Alter table with column additions, type changes, and constraints",
		},
		{
			name: "drop_and_recreate_constraints",
			initialSchema: `
CREATE TABLE AUTHORS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    EMAIL VARCHAR2(100) UNIQUE
);

CREATE TABLE BOOKS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    TITLE VARCHAR2(200) NOT NULL,
    AUTHOR_ID NUMBER NOT NULL,
    ISBN VARCHAR2(20) UNIQUE,
    PUBLISHED_YEAR NUMBER,
    PRICE NUMBER(8, 2),
    CONSTRAINT FK_AUTHOR FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHORS(ID),
    CONSTRAINT CHECK_YEAR_VALID CHECK (PUBLISHED_YEAR >= 1000 AND PUBLISHED_YEAR <= 2100),
    CONSTRAINT CHECK_PRICE_POSITIVE CHECK (PRICE > 0)
);

CREATE INDEX IDX_BOOKS_AUTHOR ON BOOKS(AUTHOR_ID);
CREATE INDEX IDX_BOOKS_YEAR ON BOOKS(PUBLISHED_YEAR);
`,
			migrationDDL: `
-- Drop and recreate foreign key with different options
ALTER TABLE BOOKS DROP CONSTRAINT FK_AUTHOR;
ALTER TABLE BOOKS ADD CONSTRAINT FK_AUTHOR_NEW FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHORS(ID) ON DELETE CASCADE;

-- Drop and modify check constraints
ALTER TABLE BOOKS DROP CONSTRAINT CHECK_YEAR_VALID;
ALTER TABLE BOOKS ADD CONSTRAINT CHECK_YEAR_EXTENDED CHECK (PUBLISHED_YEAR >= 1000 AND PUBLISHED_YEAR <= 2030);

-- Add new constraints
ALTER TABLE BOOKS ADD CONSTRAINT CHECK_TITLE_LENGTH CHECK (LENGTH(TITLE) >= 3);
`,
			description: "Drop and recreate constraints with different definitions",
		},
		{
			name: "drop_sequence",
			initialSchema: `
CREATE SEQUENCE TRANSACTION_SEQ START WITH 1000;

CREATE TABLE SIMPLE_LOG (
    ID NUMBER DEFAULT TRANSACTION_SEQ.NEXTVAL PRIMARY KEY,
    MESSAGE CLOB NOT NULL
);
`,
			migrationDDL: `
-- Drop table and sequence
DROP TABLE SIMPLE_LOG;
DROP SEQUENCE TRANSACTION_SEQ;
`,
			description: "Drop sequence and dependent table",
		},
		{
			name: "complex_view_dependencies",
			initialSchema: `
CREATE TABLE DEPARTMENTS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    MANAGER_ID NUMBER
);

CREATE TABLE EMPLOYEES (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    DEPARTMENT_ID NUMBER,
    SALARY NUMBER(10, 2),
    CONSTRAINT FK_DEPT FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS(ID)
);

-- Self-referencing foreign key
ALTER TABLE DEPARTMENTS ADD CONSTRAINT FK_MANAGER FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEES(ID);
`,
			migrationDDL: `
-- Create base view
CREATE VIEW DEPT_EMPLOYEE_COUNT AS
SELECT D.ID AS DEPT_ID, D.NAME AS DEPT_NAME, COUNT(E.ID) AS EMP_COUNT
FROM DEPARTMENTS D
LEFT JOIN EMPLOYEES E ON D.ID = E.DEPARTMENT_ID
GROUP BY D.ID, D.NAME;

-- Create dependent view
CREATE VIEW DEPT_SUMMARY AS
SELECT 
    DEC.DEPT_ID,
    DEC.DEPT_NAME,
    DEC.EMP_COUNT,
    NVL(AVG(E.SALARY), 0) AS AVG_SALARY
FROM DEPT_EMPLOYEE_COUNT DEC
LEFT JOIN EMPLOYEES E ON DEC.DEPT_ID = E.DEPARTMENT_ID
GROUP BY DEC.DEPT_ID, DEC.DEPT_NAME, DEC.EMP_COUNT;

-- Create highly dependent view
CREATE VIEW DEPT_MANAGER_SUMMARY AS
SELECT 
    DS.DEPT_ID,
    DS.DEPT_NAME,
    DS.EMP_COUNT,
    DS.AVG_SALARY,
    M.NAME AS MANAGER_NAME
FROM DEPT_SUMMARY DS
JOIN DEPARTMENTS D ON DS.DEPT_ID = D.ID
LEFT JOIN EMPLOYEES M ON D.MANAGER_ID = M.ID;

-- Create materialized view with complex dependencies
CREATE MATERIALIZED VIEW MV_DEPT_STATS AS
SELECT 
    D.ID AS DEPT_ID,
    D.NAME AS DEPT_NAME,
    COUNT(E.ID) AS TOTAL_EMPLOYEES,
    NVL(SUM(E.SALARY), 0) AS TOTAL_PAYROLL,
    NVL(AVG(E.SALARY), 0) AS AVG_SALARY,
    NVL(MAX(E.SALARY), 0) AS MAX_SALARY,
    NVL(MIN(E.SALARY), 0) AS MIN_SALARY
FROM DEPARTMENTS D
LEFT JOIN EMPLOYEES E ON D.ID = E.DEPARTMENT_ID
GROUP BY D.ID, D.NAME;

-- Create index on materialized view
CREATE INDEX IDX_MV_DEPT_PAYROLL ON MV_DEPT_STATS(TOTAL_PAYROLL DESC);
`,
			description: "Complex view dependencies with multiple levels and materialized views",
		},
		{
			name: "circular_foreign_key_dependencies",
			initialSchema: `
CREATE TABLE CUSTOMERS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    PREFERRED_ORDER_ID NUMBER
);

CREATE TABLE ORDERS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    CUSTOMER_ID NUMBER NOT NULL,
    ORDER_DATE DATE DEFAULT SYSDATE,
    TOTAL_AMOUNT NUMBER(10, 2)
);
`,
			migrationDDL: `
-- Create circular foreign key dependencies
ALTER TABLE CUSTOMERS ADD CONSTRAINT FK_PREFERRED_ORDER FOREIGN KEY (PREFERRED_ORDER_ID) REFERENCES ORDERS(ID);
ALTER TABLE ORDERS ADD CONSTRAINT FK_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(ID);

-- Add more tables with complex relationships
CREATE TABLE ORDER_ITEMS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    ORDER_ID NUMBER NOT NULL,
    PRODUCT_NAME VARCHAR2(100) NOT NULL,
    QUANTITY NUMBER NOT NULL,
    UNIT_PRICE NUMBER(10, 2) NOT NULL,
    CONSTRAINT FK_ORDER_ITEM FOREIGN KEY (ORDER_ID) REFERENCES ORDERS(ID) ON DELETE CASCADE
);

-- Create trigger to update order total
CREATE OR REPLACE TRIGGER TRG_UPDATE_ORDER_TOTAL
AFTER INSERT OR UPDATE OR DELETE ON ORDER_ITEMS
FOR EACH ROW
DECLARE
    V_ORDER_ID NUMBER;
    V_TOTAL NUMBER(10, 2);
BEGIN
    -- Get order ID
    IF INSERTING OR UPDATING THEN
        V_ORDER_ID := :NEW.ORDER_ID;
    ELSE
        V_ORDER_ID := :OLD.ORDER_ID;
    END IF;
    
    -- Calculate new total
    SELECT NVL(SUM(QUANTITY * UNIT_PRICE), 0) INTO V_TOTAL
    FROM ORDER_ITEMS
    WHERE ORDER_ID = V_ORDER_ID;
    
    -- Update order total
    UPDATE ORDERS SET TOTAL_AMOUNT = V_TOTAL WHERE ID = V_ORDER_ID;
END;
/
`,
			description: "Circular foreign key dependencies and triggers",
		},
		{
			name: "advanced_indexes_and_constraints",
			initialSchema: `
CREATE TABLE SALES_DATA (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY,
    SALE_DATE DATE NOT NULL,
    CUSTOMER_ID NUMBER NOT NULL,
    PRODUCT_ID NUMBER NOT NULL,
    AMOUNT NUMBER(10, 2) NOT NULL,
    REGION VARCHAR2(50) NOT NULL
);
`,
			migrationDDL: `
-- Create function-based index
CREATE INDEX IDX_SALES_MONTH_YEAR ON SALES_DATA(EXTRACT(YEAR FROM SALE_DATE), EXTRACT(MONTH FROM SALE_DATE));

-- Create composite index with mixed sort order
CREATE INDEX IDX_SALES_REGION_AMOUNT ON SALES_DATA(REGION ASC, AMOUNT DESC);

-- Create unique constraint on composite columns
ALTER TABLE SALES_DATA ADD CONSTRAINT UK_SALES_DATE_CUSTOMER UNIQUE (SALE_DATE, CUSTOMER_ID, PRODUCT_ID);

-- Create check constraint with complex expression
ALTER TABLE SALES_DATA ADD CONSTRAINT CHK_AMOUNT_RANGE CHECK (AMOUNT > 0 AND AMOUNT <= 999999.99);

-- Create conditional check constraint
ALTER TABLE SALES_DATA ADD CONSTRAINT CHK_REGION_VALID CHECK (REGION IN ('NORTH', 'SOUTH', 'EAST', 'WEST', 'CENTRAL'));
`,
			description: "Advanced Oracle-specific indexes and complex constraints",
		},
		{
			name: "stored_procedures_and_functions",
			initialSchema: `
CREATE TABLE AUDIT_LOG (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    TABLE_NAME VARCHAR2(100) NOT NULL,
    OPERATION VARCHAR2(10) NOT NULL,
    OLD_VALUES CLOB,
    NEW_VALUES CLOB,
    TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    USER_NAME VARCHAR2(100) DEFAULT USER
);
`,
			migrationDDL: `
-- Create stored procedure
CREATE OR REPLACE PROCEDURE LOG_AUDIT(
    P_TABLE_NAME VARCHAR2,
    P_OPERATION VARCHAR2,
    P_OLD_VALUES CLOB DEFAULT NULL,
    P_NEW_VALUES CLOB DEFAULT NULL
)
IS
BEGIN
    INSERT INTO AUDIT_LOG (TABLE_NAME, OPERATION, OLD_VALUES, NEW_VALUES)
    VALUES (P_TABLE_NAME, P_OPERATION, P_OLD_VALUES, P_NEW_VALUES);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

-- Create function with complex logic
CREATE OR REPLACE FUNCTION CALCULATE_BUSINESS_DAYS(
    P_START_DATE DATE,
    P_END_DATE DATE
) RETURN NUMBER
IS
    V_DAYS NUMBER := 0;
    V_CURRENT_DATE DATE := P_START_DATE;
BEGIN
    WHILE V_CURRENT_DATE <= P_END_DATE LOOP
        -- Skip weekends (Saturday = 7, Sunday = 1)
        IF TO_CHAR(V_CURRENT_DATE, 'D') NOT IN ('1', '7') THEN
            V_DAYS := V_DAYS + 1;
        END IF;
        V_CURRENT_DATE := V_CURRENT_DATE + 1;
    END LOOP;
    
    RETURN V_DAYS;
END;
/

-- Create another function for testing dependencies
CREATE OR REPLACE FUNCTION GET_AUDIT_COUNT(P_TABLE_NAME VARCHAR2) RETURN NUMBER
IS
    V_COUNT NUMBER;
BEGIN
    SELECT COUNT(*) INTO V_COUNT
    FROM AUDIT_LOG
    WHERE TABLE_NAME = P_TABLE_NAME;
    
    RETURN V_COUNT;
END;
/
`,
			description: "Stored procedures and functions with dependencies",
		},
		{
			name: "edge_case_column_operations",
			initialSchema: `
CREATE TABLE TEST_COLUMNS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    COL_VARCHAR VARCHAR2(50),
    COL_NUMBER NUMBER(10,2),
    COL_DATE DATE,
    COL_CLOB CLOB,
    COL_NULLABLE VARCHAR2(100),
    COL_WITH_DEFAULT VARCHAR2(50) DEFAULT 'DEFAULT_VALUE'
);
`,
			migrationDDL: `
-- Test various column modifications
ALTER TABLE TEST_COLUMNS MODIFY COL_VARCHAR VARCHAR2(200);
ALTER TABLE TEST_COLUMNS MODIFY COL_NUMBER NUMBER(15,3);
ALTER TABLE TEST_COLUMNS MODIFY COL_NULLABLE NOT NULL;
ALTER TABLE TEST_COLUMNS MODIFY COL_WITH_DEFAULT DEFAULT 'NEW_DEFAULT';

-- Add columns with various data types and constraints
ALTER TABLE TEST_COLUMNS ADD COL_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE TEST_COLUMNS ADD COL_BOOLEAN NUMBER(1) CHECK (COL_BOOLEAN IN (0,1));

-- Drop some columns
ALTER TABLE TEST_COLUMNS DROP COLUMN COL_CLOB;

-- Add constraints
ALTER TABLE TEST_COLUMNS ADD CONSTRAINT CHK_VARCHAR_LENGTH CHECK (LENGTH(COL_VARCHAR) >= 5);
ALTER TABLE TEST_COLUMNS ADD CONSTRAINT CHK_NUMBER_POSITIVE CHECK (COL_NUMBER > 0);
`,
			description: "Edge cases for column operations and data type modifications",
		},
		{
			name:          "reverse_create_tables_with_fk",
			initialSchema: "",
			migrationDDL: `
CREATE TABLE USERS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    USERNAME VARCHAR2(50) NOT NULL,
    EMAIL VARCHAR2(100) NOT NULL,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX UK_EMAIL ON USERS(EMAIL);
CREATE INDEX IDX_USERNAME ON USERS(USERNAME);

CREATE TABLE POSTS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    USER_ID NUMBER NOT NULL,
    TITLE VARCHAR2(200) NOT NULL,
    CONTENT CLOB,
    PUBLISHED_AT TIMESTAMP,
    CONSTRAINT FK_USER FOREIGN KEY (USER_ID) REFERENCES USERS(ID) ON DELETE CASCADE
);

CREATE INDEX IDX_USER_ID ON POSTS(USER_ID);
`,
			description: "Reverse: Drop tables with foreign key constraints",
		},
		{
			name: "reverse_basic_table_operations",
			initialSchema: `
CREATE TABLE USERS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    USERNAME VARCHAR2(50) NOT NULL,
    EMAIL VARCHAR2(100) NOT NULL,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    IS_ACTIVE NUMBER(1) DEFAULT 1
);

CREATE TABLE POSTS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    USER_ID NUMBER NOT NULL,
    TITLE VARCHAR2(200) NOT NULL,
    CONTENT CLOB,
    CONSTRAINT FK_USER FOREIGN KEY (USER_ID) REFERENCES USERS(ID),
    CONSTRAINT CHECK_TITLE_LENGTH CHECK (LENGTH(TITLE) > 0)
);

CREATE TABLE COMMENTS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    POST_ID NUMBER NOT NULL,
    USER_ID NUMBER NOT NULL,
    CONTENT CLOB NOT NULL,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT FK_POST FOREIGN KEY (POST_ID) REFERENCES POSTS(ID),
    CONSTRAINT FK_COMMENT_USER FOREIGN KEY (USER_ID) REFERENCES USERS(ID)
);

CREATE INDEX IDX_POSTS_USER_ID ON POSTS(USER_ID);
CREATE INDEX IDX_USERS_EMAIL ON USERS(EMAIL);
`,
			migrationDDL: `
-- Drop table
DROP TABLE COMMENTS CASCADE CONSTRAINTS;

-- Drop index
DROP INDEX IDX_USERS_EMAIL;

-- Drop check constraint
ALTER TABLE POSTS DROP CONSTRAINT CHECK_TITLE_LENGTH;

-- Drop column
ALTER TABLE USERS DROP COLUMN IS_ACTIVE;
`,
			description: "Reverse: Drop tables, columns, constraints, and indexes",
		},
		{
			name: "reverse_views_and_functions",
			initialSchema: `
CREATE TABLE PRODUCTS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    PRICE NUMBER(10, 2) NOT NULL,
    STOCK NUMBER DEFAULT 0
);

CREATE TABLE ORDERS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    PRODUCT_ID NUMBER NOT NULL,
    QUANTITY NUMBER NOT NULL,
    TOTAL NUMBER(10, 2),
    CONSTRAINT FK_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(ID)
);

CREATE VIEW PRODUCT_INVENTORY AS
SELECT 
    P.ID,
    P.NAME,
    P.PRICE,
    P.STOCK,
    NVL(SUM(O.QUANTITY), 0) AS TOTAL_ORDERED
FROM PRODUCTS P
LEFT JOIN ORDERS O ON P.ID = O.PRODUCT_ID
GROUP BY P.ID, P.NAME, P.PRICE, P.STOCK;

CREATE OR REPLACE FUNCTION CALCULATE_ORDER_TOTAL(P_PRODUCT_ID NUMBER, P_QUANTITY NUMBER)
RETURN NUMBER
IS
    V_PRICE NUMBER(10, 2);
BEGIN
    SELECT PRICE INTO V_PRICE FROM PRODUCTS WHERE ID = P_PRODUCT_ID;
    RETURN P_QUANTITY * V_PRICE;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 0;
END;
/

CREATE MATERIALIZED VIEW PRODUCT_STATS AS
SELECT 
    PRODUCT_ID,
    COUNT(*) AS ORDER_COUNT,
    SUM(QUANTITY) AS TOTAL_QUANTITY,
    SUM(TOTAL) AS TOTAL_REVENUE
FROM ORDERS
GROUP BY PRODUCT_ID;

CREATE INDEX IDX_PRODUCT_STATS_REVENUE ON PRODUCT_STATS(TOTAL_REVENUE DESC);
`,
			migrationDDL: `
-- Drop index on materialized view
DROP INDEX IDX_PRODUCT_STATS_REVENUE;

-- Drop materialized view
DROP MATERIALIZED VIEW PRODUCT_STATS;

-- Drop function
DROP FUNCTION CALCULATE_ORDER_TOTAL;

-- Drop view
DROP VIEW PRODUCT_INVENTORY;
`,
			description: "Reverse: Drop views, functions, and materialized views",
		},
		{
			name: "reverse_sequences",
			initialSchema: `
CREATE SEQUENCE ORDER_SEQ START WITH 1000 INCREMENT BY 10;

CREATE TABLE ITEMS (
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL
);

CREATE TABLE SALES_ORDERS (
    ID NUMBER DEFAULT ORDER_SEQ.NEXTVAL PRIMARY KEY,
    ITEM_ID NUMBER NOT NULL,
    QUANTITY NUMBER NOT NULL,
    CONSTRAINT FK_ITEM FOREIGN KEY (ITEM_ID) REFERENCES ITEMS(ID)
);
`,
			migrationDDL: `
-- Drop table using sequence
DROP TABLE SALES_ORDERS CASCADE CONSTRAINTS;

-- Drop sequence
DROP SEQUENCE ORDER_SEQ;
`,
			description: "Reverse: Drop sequences and tables using them",
		},
		{
			name: "reverse_drop_indexes_and_constraints",
			initialSchema: `
CREATE TABLE PRODUCTS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    CATEGORY VARCHAR2(50),
    PRICE NUMBER(10, 2),
    SUPPLIER_ID NUMBER
);
`,
			migrationDDL: `
-- Create indexes and constraints
CREATE INDEX IDX_PRODUCTS_CATEGORY ON PRODUCTS(CATEGORY);
CREATE INDEX IDX_PRODUCTS_PRICE ON PRODUCTS(PRICE);
CREATE UNIQUE INDEX IDX_PRODUCTS_NAME ON PRODUCTS(NAME);

ALTER TABLE PRODUCTS ADD CONSTRAINT CHECK_PRICE_POSITIVE CHECK (PRICE > 0);
ALTER TABLE PRODUCTS ADD CONSTRAINT CHECK_NAME_LENGTH CHECK (LENGTH(NAME) >= 3);
`,
			description: "Reverse: Create indexes and constraints on tables",
		},
		{
			name: "reverse_drop_views_and_functions",
			initialSchema: `
CREATE TABLE SALES (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    PRODUCT_NAME VARCHAR2(100) NOT NULL,
    SALE_AMOUNT NUMBER(10, 2) NOT NULL,
    SALE_DATE DATE NOT NULL
);
`,
			migrationDDL: `
-- Create views and functions
CREATE VIEW MONTHLY_SALES AS
SELECT 
    EXTRACT(YEAR FROM SALE_DATE) AS YEAR_VAL,
    EXTRACT(MONTH FROM SALE_DATE) AS MONTH_VAL,
    SUM(SALE_AMOUNT) AS TOTAL_SALES
FROM SALES
GROUP BY EXTRACT(YEAR FROM SALE_DATE), EXTRACT(MONTH FROM SALE_DATE);

CREATE MATERIALIZED VIEW TOP_PRODUCTS AS
SELECT 
    PRODUCT_NAME,
    COUNT(*) AS SALE_COUNT,
    SUM(SALE_AMOUNT) AS TOTAL_REVENUE
FROM SALES
GROUP BY PRODUCT_NAME
ORDER BY SUM(SALE_AMOUNT) DESC;

CREATE OR REPLACE FUNCTION GET_MONTHLY_TOTAL(YEAR_PARAM NUMBER, MONTH_PARAM NUMBER)
RETURN NUMBER
IS
    V_TOTAL NUMBER;
BEGIN
    SELECT NVL(SUM(SALE_AMOUNT), 0) INTO V_TOTAL
    FROM SALES
    WHERE EXTRACT(YEAR FROM SALE_DATE) = YEAR_PARAM
    AND EXTRACT(MONTH FROM SALE_DATE) = MONTH_PARAM;
    
    RETURN V_TOTAL;
END;
/

CREATE OR REPLACE FUNCTION CALCULATE_DISCOUNT(AMOUNT NUMBER)
RETURN NUMBER
IS
BEGIN
    RETURN AMOUNT * 0.1;
END;
/
`,
			description: "Reverse: Create views and functions that depend on tables",
		},
		{
			name: "reverse_alter_table_columns",
			initialSchema: `
CREATE TABLE PRODUCTS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    PRICE NUMBER(10, 2) NOT NULL,
    DESCRIPTION CLOB NOT NULL,
    CATEGORY VARCHAR2(30),
    IS_ACTIVE NUMBER(1) DEFAULT 1,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    STOCK_QUANTITY NUMBER DEFAULT 0,
    WEIGHT NUMBER(5, 2),
    CONSTRAINT CHECK_PRICE_POSITIVE CHECK (PRICE > 0),
    CONSTRAINT CHECK_STOCK_NON_NEGATIVE CHECK (STOCK_QUANTITY >= 0)
);

CREATE INDEX IDX_PRODUCTS_CATEGORY ON PRODUCTS(CATEGORY);
CREATE INDEX IDX_PRODUCTS_PRICE ON PRODUCTS(PRICE);
CREATE INDEX IDX_PRODUCTS_CREATED_AT ON PRODUCTS(CREATED_AT);
CREATE UNIQUE INDEX IDX_PRODUCTS_NAME_CATEGORY ON PRODUCTS(NAME, CATEGORY);
`,
			migrationDDL: `
-- Drop indexes
DROP INDEX IDX_PRODUCTS_NAME_CATEGORY;
DROP INDEX IDX_PRODUCTS_CREATED_AT;

-- Drop constraints
ALTER TABLE PRODUCTS DROP CONSTRAINT CHECK_STOCK_NON_NEGATIVE;
ALTER TABLE PRODUCTS DROP CONSTRAINT CHECK_PRICE_POSITIVE;

-- Revert column changes
ALTER TABLE PRODUCTS MODIFY DESCRIPTION NULL;
ALTER TABLE PRODUCTS MODIFY PRICE NUMBER(8, 2);
ALTER TABLE PRODUCTS MODIFY NAME VARCHAR2(50);

-- Drop columns
ALTER TABLE PRODUCTS DROP COLUMN WEIGHT;
ALTER TABLE PRODUCTS DROP COLUMN STOCK_QUANTITY;
ALTER TABLE PRODUCTS DROP COLUMN CREATED_AT;
`,
			description: "Reverse: Remove columns, constraints and revert type changes",
		},
		{
			name: "reverse_drop_and_recreate_constraints",
			initialSchema: `
CREATE TABLE AUTHORS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    EMAIL VARCHAR2(100) UNIQUE
);

CREATE TABLE BOOKS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    TITLE VARCHAR2(200) NOT NULL,
    AUTHOR_ID NUMBER NOT NULL,
    ISBN VARCHAR2(20) UNIQUE,
    PUBLISHED_YEAR NUMBER,
    PRICE NUMBER(8, 2),
    CONSTRAINT FK_AUTHOR_NEW FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHORS(ID) ON DELETE CASCADE,
    CONSTRAINT CHECK_YEAR_EXTENDED CHECK (PUBLISHED_YEAR >= 1000 AND PUBLISHED_YEAR <= 2030),
    CONSTRAINT CHECK_TITLE_LENGTH CHECK (LENGTH(TITLE) >= 3)
);

CREATE INDEX IDX_BOOKS_AUTHOR ON BOOKS(AUTHOR_ID);
CREATE INDEX IDX_BOOKS_YEAR ON BOOKS(PUBLISHED_YEAR);
`,
			migrationDDL: `
-- Drop new constraints
ALTER TABLE BOOKS DROP CONSTRAINT CHECK_TITLE_LENGTH;
ALTER TABLE BOOKS DROP CONSTRAINT CHECK_YEAR_EXTENDED;
ALTER TABLE BOOKS DROP CONSTRAINT FK_AUTHOR_NEW;

-- Recreate original constraints
ALTER TABLE BOOKS ADD CONSTRAINT FK_AUTHOR FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHORS(ID);
ALTER TABLE BOOKS ADD CONSTRAINT CHECK_YEAR_VALID CHECK (PUBLISHED_YEAR >= 1000 AND PUBLISHED_YEAR <= 2100);
ALTER TABLE BOOKS ADD CONSTRAINT CHECK_PRICE_POSITIVE CHECK (PRICE > 0);
`,
			description: "Reverse: Revert constraint modifications",
		},
		{
			name:          "reverse_drop_sequence",
			initialSchema: "",
			migrationDDL: `
-- Create sequence and table
CREATE SEQUENCE TRANSACTION_SEQ START WITH 1000;

CREATE TABLE SIMPLE_LOG (
    ID NUMBER DEFAULT TRANSACTION_SEQ.NEXTVAL PRIMARY KEY,
    MESSAGE CLOB NOT NULL
);
`,
			description: "Reverse: Create sequence and dependent table",
		},
		{
			name: "reverse_complex_view_dependencies",
			initialSchema: `
CREATE TABLE DEPARTMENTS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    MANAGER_ID NUMBER
);

CREATE TABLE EMPLOYEES (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    DEPARTMENT_ID NUMBER,
    SALARY NUMBER(10, 2),
    CONSTRAINT FK_DEPT FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS(ID)
);

ALTER TABLE DEPARTMENTS ADD CONSTRAINT FK_MANAGER FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEES(ID);

CREATE VIEW DEPT_EMPLOYEE_COUNT AS
SELECT D.ID AS DEPT_ID, D.NAME AS DEPT_NAME, COUNT(E.ID) AS EMP_COUNT
FROM DEPARTMENTS D
LEFT JOIN EMPLOYEES E ON D.ID = E.DEPARTMENT_ID
GROUP BY D.ID, D.NAME;

CREATE VIEW DEPT_SUMMARY AS
SELECT 
    DEC.DEPT_ID,
    DEC.DEPT_NAME,
    DEC.EMP_COUNT,
    NVL(AVG(E.SALARY), 0) AS AVG_SALARY
FROM DEPT_EMPLOYEE_COUNT DEC
LEFT JOIN EMPLOYEES E ON DEC.DEPT_ID = E.DEPARTMENT_ID
GROUP BY DEC.DEPT_ID, DEC.DEPT_NAME, DEC.EMP_COUNT;

CREATE VIEW DEPT_MANAGER_SUMMARY AS
SELECT 
    DS.DEPT_ID,
    DS.DEPT_NAME,
    DS.EMP_COUNT,
    DS.AVG_SALARY,
    M.NAME AS MANAGER_NAME
FROM DEPT_SUMMARY DS
JOIN DEPARTMENTS D ON DS.DEPT_ID = D.ID
LEFT JOIN EMPLOYEES M ON D.MANAGER_ID = M.ID;

CREATE MATERIALIZED VIEW MV_DEPT_STATS AS
SELECT 
    D.ID AS DEPT_ID,
    D.NAME AS DEPT_NAME,
    COUNT(E.ID) AS TOTAL_EMPLOYEES,
    NVL(SUM(E.SALARY), 0) AS TOTAL_PAYROLL,
    NVL(AVG(E.SALARY), 0) AS AVG_SALARY,
    NVL(MAX(E.SALARY), 0) AS MAX_SALARY,
    NVL(MIN(E.SALARY), 0) AS MIN_SALARY
FROM DEPARTMENTS D
LEFT JOIN EMPLOYEES E ON D.ID = E.DEPARTMENT_ID
GROUP BY D.ID, D.NAME;

CREATE INDEX IDX_MV_DEPT_PAYROLL ON MV_DEPT_STATS(TOTAL_PAYROLL DESC);
`,
			migrationDDL: `
-- Drop index on materialized view
DROP INDEX IDX_MV_DEPT_PAYROLL;

-- Drop materialized view
DROP MATERIALIZED VIEW MV_DEPT_STATS;

-- Drop views in dependency order
DROP VIEW DEPT_MANAGER_SUMMARY;
DROP VIEW DEPT_SUMMARY;
DROP VIEW DEPT_EMPLOYEE_COUNT;
`,
			description: "Reverse: Drop complex view dependencies",
		},
		{
			name: "reverse_circular_foreign_key_dependencies",
			initialSchema: `
CREATE TABLE CUSTOMERS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    PREFERRED_ORDER_ID NUMBER
);

CREATE TABLE ORDERS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    CUSTOMER_ID NUMBER NOT NULL,
    ORDER_DATE DATE DEFAULT SYSDATE,
    TOTAL_AMOUNT NUMBER(10, 2)
);

ALTER TABLE CUSTOMERS ADD CONSTRAINT FK_PREFERRED_ORDER FOREIGN KEY (PREFERRED_ORDER_ID) REFERENCES ORDERS(ID);
ALTER TABLE ORDERS ADD CONSTRAINT FK_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(ID);

CREATE TABLE ORDER_ITEMS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    ORDER_ID NUMBER NOT NULL,
    PRODUCT_NAME VARCHAR2(100) NOT NULL,
    QUANTITY NUMBER NOT NULL,
    UNIT_PRICE NUMBER(10, 2) NOT NULL,
    CONSTRAINT FK_ORDER_ITEM FOREIGN KEY (ORDER_ID) REFERENCES ORDERS(ID) ON DELETE CASCADE
);

CREATE OR REPLACE TRIGGER TRG_UPDATE_ORDER_TOTAL
AFTER INSERT OR UPDATE OR DELETE ON ORDER_ITEMS
FOR EACH ROW
DECLARE
    V_ORDER_ID NUMBER;
    V_TOTAL NUMBER(10, 2);
BEGIN
    -- Get order ID
    IF INSERTING OR UPDATING THEN
        V_ORDER_ID := :NEW.ORDER_ID;
    ELSE
        V_ORDER_ID := :OLD.ORDER_ID;
    END IF;
    
    -- Calculate new total
    SELECT NVL(SUM(QUANTITY * UNIT_PRICE), 0) INTO V_TOTAL
    FROM ORDER_ITEMS
    WHERE ORDER_ID = V_ORDER_ID;
    
    -- Update order total
    UPDATE ORDERS SET TOTAL_AMOUNT = V_TOTAL WHERE ID = V_ORDER_ID;
END;
/
`,
			migrationDDL: `
-- Drop trigger
DROP TRIGGER TRG_UPDATE_ORDER_TOTAL;

-- Drop table with foreign key
DROP TABLE ORDER_ITEMS CASCADE CONSTRAINTS;

-- Drop circular foreign keys
ALTER TABLE ORDERS DROP CONSTRAINT FK_CUSTOMER;
ALTER TABLE CUSTOMERS DROP CONSTRAINT FK_PREFERRED_ORDER;
`,
			description: "Reverse: Remove circular foreign key dependencies and triggers",
		},
		{
			name: "reverse_advanced_indexes_and_constraints",
			initialSchema: `
CREATE TABLE SALES_DATA (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY,
    SALE_DATE DATE NOT NULL,
    CUSTOMER_ID NUMBER NOT NULL,
    PRODUCT_ID NUMBER NOT NULL,
    AMOUNT NUMBER(10, 2) NOT NULL,
    REGION VARCHAR2(50) NOT NULL,
    CONSTRAINT UK_SALES_DATE_CUSTOMER UNIQUE (SALE_DATE, CUSTOMER_ID, PRODUCT_ID),
    CONSTRAINT CHK_AMOUNT_RANGE CHECK (AMOUNT > 0 AND AMOUNT <= 999999.99),
    CONSTRAINT CHK_REGION_VALID CHECK (REGION IN ('NORTH', 'SOUTH', 'EAST', 'WEST', 'CENTRAL'))
);

CREATE INDEX IDX_SALES_MONTH_YEAR ON SALES_DATA(EXTRACT(YEAR FROM SALE_DATE), EXTRACT(MONTH FROM SALE_DATE));
CREATE INDEX IDX_SALES_REGION_AMOUNT ON SALES_DATA(REGION ASC, AMOUNT DESC);
`,
			migrationDDL: `
-- Drop indexes
DROP INDEX IDX_SALES_REGION_AMOUNT;
DROP INDEX IDX_SALES_MONTH_YEAR;

-- Drop constraints
ALTER TABLE SALES_DATA DROP CONSTRAINT CHK_REGION_VALID;
ALTER TABLE SALES_DATA DROP CONSTRAINT CHK_AMOUNT_RANGE;
ALTER TABLE SALES_DATA DROP CONSTRAINT UK_SALES_DATE_CUSTOMER;
`,
			description: "Reverse: Remove advanced indexes and complex constraints",
		},
		{
			name: "reverse_stored_procedures_and_functions",
			initialSchema: `
CREATE TABLE AUDIT_LOG (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    TABLE_NAME VARCHAR2(100) NOT NULL,
    OPERATION VARCHAR2(10) NOT NULL,
    OLD_VALUES CLOB,
    NEW_VALUES CLOB,
    TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    USER_NAME VARCHAR2(100) DEFAULT USER
);

CREATE OR REPLACE PROCEDURE LOG_AUDIT(
    P_TABLE_NAME VARCHAR2,
    P_OPERATION VARCHAR2,
    P_OLD_VALUES CLOB DEFAULT NULL,
    P_NEW_VALUES CLOB DEFAULT NULL
)
IS
BEGIN
    INSERT INTO AUDIT_LOG (TABLE_NAME, OPERATION, OLD_VALUES, NEW_VALUES)
    VALUES (P_TABLE_NAME, P_OPERATION, P_OLD_VALUES, P_NEW_VALUES);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

CREATE OR REPLACE FUNCTION CALCULATE_BUSINESS_DAYS(
    P_START_DATE DATE,
    P_END_DATE DATE
) RETURN NUMBER
IS
    V_DAYS NUMBER := 0;
    V_CURRENT_DATE DATE := P_START_DATE;
BEGIN
    WHILE V_CURRENT_DATE <= P_END_DATE LOOP
        -- Skip weekends (Saturday = 7, Sunday = 1)
        IF TO_CHAR(V_CURRENT_DATE, 'D') NOT IN ('1', '7') THEN
            V_DAYS := V_DAYS + 1;
        END IF;
        V_CURRENT_DATE := V_CURRENT_DATE + 1;
    END LOOP;
    
    RETURN V_DAYS;
END;
/

CREATE OR REPLACE FUNCTION GET_AUDIT_COUNT(P_TABLE_NAME VARCHAR2) RETURN NUMBER
IS
    V_COUNT NUMBER;
BEGIN
    SELECT COUNT(*) INTO V_COUNT
    FROM AUDIT_LOG
    WHERE TABLE_NAME = P_TABLE_NAME;
    
    RETURN V_COUNT;
END;
/
`,
			migrationDDL: `
-- Drop functions and procedures
DROP FUNCTION GET_AUDIT_COUNT;
DROP FUNCTION CALCULATE_BUSINESS_DAYS;
DROP PROCEDURE LOG_AUDIT;
`,
			description: "Reverse: Remove stored procedures and functions",
		},
		{
			name: "reverse_edge_case_column_operations",
			initialSchema: `
CREATE TABLE TEST_COLUMNS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    COL_VARCHAR VARCHAR2(200) NOT NULL,
    COL_NUMBER NUMBER(15,3),
    COL_DATE DATE,
    COL_NULLABLE VARCHAR2(100) NOT NULL,
    COL_WITH_DEFAULT VARCHAR2(50) DEFAULT 'NEW_DEFAULT',
    COL_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    COL_BOOLEAN NUMBER(1) CHECK (COL_BOOLEAN IN (0,1)),
    CONSTRAINT CHK_VARCHAR_LENGTH CHECK (LENGTH(COL_VARCHAR) >= 5),
    CONSTRAINT CHK_NUMBER_POSITIVE CHECK (COL_NUMBER > 0)
);
`,
			migrationDDL: `
-- Drop constraints
ALTER TABLE TEST_COLUMNS DROP CONSTRAINT CHK_NUMBER_POSITIVE;
ALTER TABLE TEST_COLUMNS DROP CONSTRAINT CHK_VARCHAR_LENGTH;

-- Drop columns
ALTER TABLE TEST_COLUMNS DROP COLUMN COL_BOOLEAN;
ALTER TABLE TEST_COLUMNS DROP COLUMN COL_TIMESTAMP;

-- Add back dropped column
ALTER TABLE TEST_COLUMNS ADD COL_CLOB CLOB;

-- Revert column modifications
ALTER TABLE TEST_COLUMNS MODIFY COL_WITH_DEFAULT DEFAULT 'DEFAULT_VALUE';
ALTER TABLE TEST_COLUMNS MODIFY COL_NULLABLE NULL;
ALTER TABLE TEST_COLUMNS MODIFY COL_NUMBER NUMBER(10,2);
ALTER TABLE TEST_COLUMNS MODIFY COL_VARCHAR VARCHAR2(50);
`,
			description: "Reverse: Revert edge case column operations",
		},
		{
			name: "table_and_column_comments",
			initialSchema: `
CREATE TABLE PRODUCTS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    PRICE NUMBER(10, 2) NOT NULL,
    DESCRIPTION CLOB,
    CATEGORY VARCHAR2(50),
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE CUSTOMERS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    EMAIL VARCHAR2(100),
    PHONE VARCHAR2(20),
    ADDRESS CLOB
);

CREATE INDEX IDX_PRODUCTS_CATEGORY ON PRODUCTS(CATEGORY);
CREATE UNIQUE INDEX IDX_CUSTOMERS_EMAIL ON CUSTOMERS(EMAIL);
`,
			migrationDDL: `
-- Add table comments
COMMENT ON TABLE PRODUCTS IS 'Product catalog table containing all product information';
COMMENT ON TABLE CUSTOMERS IS 'Customer information and contact details';

-- Add column comments
COMMENT ON COLUMN PRODUCTS.ID IS 'Unique product identifier (auto-generated)';
COMMENT ON COLUMN PRODUCTS.NAME IS 'Product name - must be unique and descriptive';
COMMENT ON COLUMN PRODUCTS.PRICE IS 'Product price in USD with 2 decimal places';
COMMENT ON COLUMN PRODUCTS.DESCRIPTION IS 'Detailed product description with HTML formatting support';
COMMENT ON COLUMN PRODUCTS.CATEGORY IS 'Product category for filtering and organization';
COMMENT ON COLUMN PRODUCTS.CREATED_AT IS 'Timestamp when the product was added to catalog';

COMMENT ON COLUMN CUSTOMERS.ID IS 'Primary key for customer records';
COMMENT ON COLUMN CUSTOMERS.NAME IS 'Full name of the customer';
COMMENT ON COLUMN CUSTOMERS.EMAIL IS 'Customer email address (unique)';
COMMENT ON COLUMN CUSTOMERS.PHONE IS 'Contact phone number';
COMMENT ON COLUMN CUSTOMERS.ADDRESS IS 'Full mailing address with multiple lines';

-- Create new table with comments immediately
CREATE TABLE ORDERS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    CUSTOMER_ID NUMBER NOT NULL,
    PRODUCT_ID NUMBER NOT NULL,
    ORDER_DATE DATE DEFAULT SYSDATE,
    QUANTITY NUMBER NOT NULL,
    TOTAL_AMOUNT NUMBER(12, 2),
    STATUS VARCHAR2(20) DEFAULT 'PENDING',
    CONSTRAINT FK_ORDER_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(ID),
    CONSTRAINT FK_ORDER_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(ID)
);

-- Add comments to the new table
COMMENT ON TABLE ORDERS IS 'Customer orders linking products and customers';
COMMENT ON COLUMN ORDERS.ID IS 'Unique order identifier';
COMMENT ON COLUMN ORDERS.CUSTOMER_ID IS 'Reference to customer who placed the order';
COMMENT ON COLUMN ORDERS.PRODUCT_ID IS 'Reference to ordered product';
COMMENT ON COLUMN ORDERS.ORDER_DATE IS 'Date when the order was placed';
COMMENT ON COLUMN ORDERS.QUANTITY IS 'Number of items ordered';
COMMENT ON COLUMN ORDERS.TOTAL_AMOUNT IS 'Total order amount including taxes';
COMMENT ON COLUMN ORDERS.STATUS IS 'Current order status (PENDING, SHIPPED, DELIVERED, CANCELLED)';
`,
			description: "Add comprehensive comments to tables and columns using Oracle COMMENT syntax",
		},
		{
			name: "modify_and_drop_comments",
			initialSchema: `
CREATE TABLE INVENTORY (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    PRODUCT_CODE VARCHAR2(50) NOT NULL,
    STOCK_LEVEL NUMBER NOT NULL,
    MIN_STOCK NUMBER DEFAULT 10,
    MAX_STOCK NUMBER DEFAULT 1000,
    WAREHOUSE_ID NUMBER,
    LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add initial comments
COMMENT ON TABLE INVENTORY IS 'Initial inventory tracking table';
COMMENT ON COLUMN INVENTORY.ID IS 'Primary key for inventory records';
COMMENT ON COLUMN INVENTORY.PRODUCT_CODE IS 'Product identification code';
COMMENT ON COLUMN INVENTORY.STOCK_LEVEL IS 'Current stock quantity';
COMMENT ON COLUMN INVENTORY.MIN_STOCK IS 'Minimum stock threshold';
COMMENT ON COLUMN INVENTORY.MAX_STOCK IS 'Maximum stock capacity';
COMMENT ON COLUMN INVENTORY.WAREHOUSE_ID IS 'Warehouse location identifier';

CREATE TABLE SUPPLIERS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    CONTACT_EMAIL VARCHAR2(100),
    RATING NUMBER(2,1)
);

COMMENT ON TABLE SUPPLIERS IS 'Supplier information table';
COMMENT ON COLUMN SUPPLIERS.NAME IS 'Supplier company name';
COMMENT ON COLUMN SUPPLIERS.CONTACT_EMAIL IS 'Primary contact email';
`,
			migrationDDL: `
-- Modify existing table comment
COMMENT ON TABLE INVENTORY IS 'Updated inventory management system with enhanced tracking capabilities';

-- Modify existing column comments
COMMENT ON COLUMN INVENTORY.PRODUCT_CODE IS 'Updated: Standardized product SKU following new naming convention';
COMMENT ON COLUMN INVENTORY.STOCK_LEVEL IS 'Updated: Real-time stock quantity with automated updates';
COMMENT ON COLUMN INVENTORY.MIN_STOCK IS 'Updated: Dynamic minimum threshold based on sales velocity';

-- Remove comments by setting to empty string
COMMENT ON COLUMN INVENTORY.MAX_STOCK IS '';
COMMENT ON COLUMN INVENTORY.WAREHOUSE_ID IS '';

-- Remove table comment
COMMENT ON TABLE SUPPLIERS IS '';

-- Remove specific column comments using empty string
COMMENT ON COLUMN SUPPLIERS.CONTACT_EMAIL IS '';

-- Add new comment to previously uncommented column
COMMENT ON COLUMN INVENTORY.LAST_UPDATED IS 'Timestamp of last inventory update or modification';

-- Add new column with comment
ALTER TABLE INVENTORY ADD REORDER_POINT NUMBER DEFAULT 0;
COMMENT ON COLUMN INVENTORY.REORDER_POINT IS 'Calculated reorder point based on lead time and demand';

-- Modify existing column comment after it was cleared
COMMENT ON COLUMN SUPPLIERS.RATING IS 'Supplier performance rating (1.0 to 5.0 scale)';
`,
			description: "Modify existing comments and remove comments using empty string",
		},
		{
			name: "comments_with_special_characters",
			initialSchema: `
CREATE TABLE SPECIAL_DATA (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    DATA_VALUE VARCHAR2(500),
    METADATA_JSON CLOB,
    UNICODE_TEXT NVARCHAR2(200),
    BINARY_DATA BLOB,
    CATEGORY VARCHAR2(100),
    STATUS VARCHAR2(50)
);

CREATE TABLE MULTILANG_CONTENT (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    TITLE_EN VARCHAR2(200),
    TITLE_ES VARCHAR2(200),
    TITLE_ZH NVARCHAR2(200),
    CONTENT_TEXT CLOB,
    TAGS VARCHAR2(1000)
);
`,
			migrationDDL: `
-- Comments with single quotes and escaping
COMMENT ON TABLE SPECIAL_DATA IS 'Table containing "special" data with various formats & types';
COMMENT ON COLUMN SPECIAL_DATA.DATA_VALUE IS 'Data value field - supports NULL, empty strings, and ''quoted'' content';

-- Comments with double quotes
COMMENT ON COLUMN SPECIAL_DATA.METADATA_JSON IS 'JSON metadata: {"key": "value", "array": [1,2,3]}';

-- Multi-line comments with line breaks and special characters
COMMENT ON COLUMN SPECIAL_DATA.UNICODE_TEXT IS 'Unicode text support:
Line 1: Basic ASCII text
Line 2: Special chars !@#$%^&*()_+-={}[]|;:,.<>?
Line 3: Math symbols ≤ ≥ ± × ÷ ∑ ∏ ∆ π
Line 4: Currencies $ € £ ¥ ₹';

-- Comments with backslashes and escape sequences
COMMENT ON COLUMN SPECIAL_DATA.BINARY_DATA IS 'Binary data storage\nSupports: images, documents, archives\tTab-separated info\r\nWindows line endings';

-- Comments with percent signs and wildcards
COMMENT ON COLUMN SPECIAL_DATA.CATEGORY IS 'Category field: use % for wildcard searches, _ for single char matching';

-- Comments with SQL injection-like patterns (should be safely handled)
COMMENT ON COLUMN SPECIAL_DATA.STATUS IS 'Status field - values: ''ACTIVE'', ''INACTIVE'', ''PENDING''; DROP TABLE users; --';

-- Unicode comments with international characters
COMMENT ON TABLE MULTILANG_CONTENT IS 'Multilingual content: English, Español, 中文, العربية, Русский, 日本語';
COMMENT ON COLUMN MULTILANG_CONTENT.TITLE_EN IS 'English title field';
COMMENT ON COLUMN MULTILANG_CONTENT.TITLE_ES IS 'Título en español';
COMMENT ON COLUMN MULTILANG_CONTENT.TITLE_ZH IS '中文标题字段 - 支持简体和繁体中文';

-- Long comment with mixed content
COMMENT ON COLUMN MULTILANG_CONTENT.CONTENT_TEXT IS 'Content field supporting:
1. HTML tags: <b>bold</b>, <i>italic</i>, <a href="http://example.com">links</a>
2. Markdown: **bold**, *italic*, [link](url), backtick-code-backtick
3. Special symbols: © ® ™ § ¶ † ‡ • … ‰ ′ ″
4. Emojis: 😀 😃 😄 😁 🚀 ⭐ 🎉 💯
5. Mathematical: ∀x∈ℝ: x² ≥ 0, ∑(i=1 to n) i = n(n+1)/2';

-- Comment with XML/HTML content
COMMENT ON COLUMN MULTILANG_CONTENT.TAGS IS 'XML tags: <tag type="category" value="news"/><tag type="priority" value="high"/>End of XML';

-- Create additional table to test more complex scenarios
CREATE TABLE COMPLEX_TYPES (
    ID NUMBER PRIMARY KEY,
    XML_DATA XMLTYPE,
    TIMESTAMP_TZ TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP,
    INTERVAL_DS INTERVAL DAY TO SECOND,
    RAW_DATA RAW(100),
    LONG_RAW_DATA LONG RAW
);

-- Comments with technical specifications
COMMENT ON TABLE COMPLEX_TYPES IS 'Advanced data types testing table
Supports: XMLType, Timestamps with timezone, Intervals, RAW data
Performance: Optimized for OLTP workloads @ 10K+ TPS
Security: Row-level security enabled, audit trails maintained';

COMMENT ON COLUMN COMPLEX_TYPES.XML_DATA IS 'XMLType column for structured XML storage
Example: <?xml version="1.0" encoding="UTF-8"?>
<root><item id="1" name="test">Content</item></root>';

COMMENT ON COLUMN COMPLEX_TYPES.TIMESTAMP_TZ IS 'Timestamp with timezone: YYYY-MM-DD HH24:MI:SS.FF TZR
Default: SYSTIMESTAMP (system timestamp with session timezone)';

COMMENT ON COLUMN COMPLEX_TYPES.INTERVAL_DS IS 'Interval Day to Second: +/-DD HH:MI:SS.FFFFFF
Range: +/-999999999 days, precision up to nanoseconds';
`,
			description: "Test comments with special characters, quotes, Unicode, multi-line text, and complex content",
		},
		{
			name: "reverse_table_and_column_comments",
			initialSchema: `
CREATE TABLE PRODUCTS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    PRICE NUMBER(10, 2) NOT NULL,
    DESCRIPTION CLOB,
    CATEGORY VARCHAR2(50),
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE CUSTOMERS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    EMAIL VARCHAR2(100),
    PHONE VARCHAR2(20),
    ADDRESS CLOB
);

CREATE TABLE ORDERS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    CUSTOMER_ID NUMBER NOT NULL,
    PRODUCT_ID NUMBER NOT NULL,
    ORDER_DATE DATE DEFAULT SYSDATE,
    QUANTITY NUMBER NOT NULL,
    TOTAL_AMOUNT NUMBER(12, 2),
    STATUS VARCHAR2(20) DEFAULT 'PENDING',
    CONSTRAINT FK_ORDER_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(ID),
    CONSTRAINT FK_ORDER_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(ID)
);

CREATE INDEX IDX_PRODUCTS_CATEGORY ON PRODUCTS(CATEGORY);
CREATE UNIQUE INDEX IDX_CUSTOMERS_EMAIL ON CUSTOMERS(EMAIL);

-- Add all comments
COMMENT ON TABLE PRODUCTS IS 'Product catalog table containing all product information';
COMMENT ON TABLE CUSTOMERS IS 'Customer information and contact details';
COMMENT ON COLUMN PRODUCTS.ID IS 'Unique product identifier (auto-generated)';
COMMENT ON COLUMN PRODUCTS.NAME IS 'Product name - must be unique and descriptive';
COMMENT ON COLUMN PRODUCTS.PRICE IS 'Product price in USD with 2 decimal places';
COMMENT ON COLUMN PRODUCTS.DESCRIPTION IS 'Detailed product description with HTML formatting support';
COMMENT ON COLUMN PRODUCTS.CATEGORY IS 'Product category for filtering and organization';
COMMENT ON COLUMN PRODUCTS.CREATED_AT IS 'Timestamp when the product was added to catalog';
COMMENT ON COLUMN CUSTOMERS.ID IS 'Primary key for customer records';
COMMENT ON COLUMN CUSTOMERS.NAME IS 'Full name of the customer';
COMMENT ON COLUMN CUSTOMERS.EMAIL IS 'Customer email address (unique)';
COMMENT ON COLUMN CUSTOMERS.PHONE IS 'Contact phone number';
COMMENT ON COLUMN CUSTOMERS.ADDRESS IS 'Full mailing address with multiple lines';
COMMENT ON TABLE ORDERS IS 'Customer orders linking products and customers';
COMMENT ON COLUMN ORDERS.ID IS 'Unique order identifier';
COMMENT ON COLUMN ORDERS.CUSTOMER_ID IS 'Reference to customer who placed the order';
COMMENT ON COLUMN ORDERS.PRODUCT_ID IS 'Reference to ordered product';
COMMENT ON COLUMN ORDERS.ORDER_DATE IS 'Date when the order was placed';
COMMENT ON COLUMN ORDERS.QUANTITY IS 'Number of items ordered';
COMMENT ON COLUMN ORDERS.TOTAL_AMOUNT IS 'Total order amount including taxes';
COMMENT ON COLUMN ORDERS.STATUS IS 'Current order status (PENDING, SHIPPED, DELIVERED, CANCELLED)';
`,
			migrationDDL: `
-- Remove all comments
COMMENT ON COLUMN ORDERS.STATUS IS '';
COMMENT ON COLUMN ORDERS.TOTAL_AMOUNT IS '';
COMMENT ON COLUMN ORDERS.QUANTITY IS '';
COMMENT ON COLUMN ORDERS.ORDER_DATE IS '';
COMMENT ON COLUMN ORDERS.PRODUCT_ID IS '';
COMMENT ON COLUMN ORDERS.CUSTOMER_ID IS '';
COMMENT ON COLUMN ORDERS.ID IS '';
COMMENT ON TABLE ORDERS IS '';

COMMENT ON COLUMN CUSTOMERS.ADDRESS IS '';
COMMENT ON COLUMN CUSTOMERS.PHONE IS '';
COMMENT ON COLUMN CUSTOMERS.EMAIL IS '';
COMMENT ON COLUMN CUSTOMERS.NAME IS '';
COMMENT ON COLUMN CUSTOMERS.ID IS '';

COMMENT ON COLUMN PRODUCTS.CREATED_AT IS '';
COMMENT ON COLUMN PRODUCTS.CATEGORY IS '';
COMMENT ON COLUMN PRODUCTS.DESCRIPTION IS '';
COMMENT ON COLUMN PRODUCTS.PRICE IS '';
COMMENT ON COLUMN PRODUCTS.NAME IS '';
COMMENT ON COLUMN PRODUCTS.ID IS '';

COMMENT ON TABLE CUSTOMERS IS '';
COMMENT ON TABLE PRODUCTS IS '';

-- Drop the ORDERS table
DROP TABLE ORDERS CASCADE CONSTRAINTS;
`,
			description: "Reverse: Remove all comments and drop a table",
		},
		{
			name: "reverse_modify_and_drop_comments",
			initialSchema: `
CREATE TABLE INVENTORY (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    PRODUCT_CODE VARCHAR2(50) NOT NULL,
    STOCK_LEVEL NUMBER NOT NULL,
    MIN_STOCK NUMBER DEFAULT 10,
    MAX_STOCK NUMBER DEFAULT 1000,
    WAREHOUSE_ID NUMBER,
    LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    REORDER_POINT NUMBER DEFAULT 0
);

CREATE TABLE SUPPLIERS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    CONTACT_EMAIL VARCHAR2(100),
    RATING NUMBER(2,1)
);

-- Modified comments
COMMENT ON TABLE INVENTORY IS 'Updated inventory management system with enhanced tracking capabilities';
COMMENT ON COLUMN INVENTORY.PRODUCT_CODE IS 'Updated: Standardized product SKU following new naming convention';
COMMENT ON COLUMN INVENTORY.STOCK_LEVEL IS 'Updated: Real-time stock quantity with automated updates';
COMMENT ON COLUMN INVENTORY.MIN_STOCK IS 'Updated: Dynamic minimum threshold based on sales velocity';
COMMENT ON COLUMN INVENTORY.LAST_UPDATED IS 'Timestamp of last inventory update or modification';
COMMENT ON COLUMN INVENTORY.REORDER_POINT IS 'Calculated reorder point based on lead time and demand';
COMMENT ON COLUMN SUPPLIERS.RATING IS 'Supplier performance rating (1.0 to 5.0 scale)';
`,
			migrationDDL: `
-- Drop column with comment
ALTER TABLE INVENTORY DROP COLUMN REORDER_POINT;

-- Add comment to new column
COMMENT ON COLUMN INVENTORY.WAREHOUSE_ID IS 'Warehouse location identifier';

-- Restore original comment
COMMENT ON COLUMN INVENTORY.MIN_STOCK IS 'Minimum stock threshold';

-- Remove other updated comments
COMMENT ON COLUMN INVENTORY.STOCK_LEVEL IS 'Current stock quantity';
COMMENT ON COLUMN INVENTORY.PRODUCT_CODE IS 'Product identification code';

-- Add comments that were removed
COMMENT ON TABLE SUPPLIERS IS 'Supplier information table';
COMMENT ON COLUMN SUPPLIERS.CONTACT_EMAIL IS 'Primary contact email';

-- Restore other columns that had comments removed
COMMENT ON COLUMN INVENTORY.MAX_STOCK IS 'Maximum stock capacity';

-- Change table comment
COMMENT ON TABLE INVENTORY IS 'Initial inventory tracking table';

-- Remove comments
COMMENT ON COLUMN SUPPLIERS.RATING IS '';
COMMENT ON COLUMN INVENTORY.LAST_UPDATED IS '';
`,
			description: "Reverse: Restore original comments and modify state",
		},
		{
			name: "reverse_comments_with_special_characters",
			initialSchema: `
CREATE TABLE SPECIAL_DATA (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    DATA_VALUE VARCHAR2(500),
    METADATA_JSON CLOB,
    UNICODE_TEXT NVARCHAR2(200),
    BINARY_DATA BLOB,
    CATEGORY VARCHAR2(100),
    STATUS VARCHAR2(50)
);

CREATE TABLE MULTILANG_CONTENT (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    TITLE_EN VARCHAR2(200),
    TITLE_ES VARCHAR2(200),
    TITLE_ZH NVARCHAR2(200),
    CONTENT_TEXT CLOB,
    TAGS VARCHAR2(1000)
);

CREATE TABLE COMPLEX_TYPES (
    ID NUMBER PRIMARY KEY,
    XML_DATA XMLTYPE,
    TIMESTAMP_TZ TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP,
    INTERVAL_DS INTERVAL DAY TO SECOND,
    RAW_DATA RAW(100),
    LONG_RAW_DATA LONG RAW
);

-- All comments added
COMMENT ON TABLE SPECIAL_DATA IS 'Table containing "special" data with various formats & types';
COMMENT ON COLUMN SPECIAL_DATA.DATA_VALUE IS 'Data value field - supports NULL, empty strings, and ''quoted'' content';
COMMENT ON COLUMN SPECIAL_DATA.METADATA_JSON IS 'JSON metadata: {"key": "value", "array": [1,2,3]}';
COMMENT ON COLUMN SPECIAL_DATA.UNICODE_TEXT IS 'Unicode text support:
Line 1: Basic ASCII text
Line 2: Special chars !@#$%^&*()_+-={}[]|;:,.<>?
Line 3: Math symbols ≤ ≥ ± × ÷ ∑ ∏ ∆ π
Line 4: Currencies $ € £ ¥ ₹';
COMMENT ON COLUMN SPECIAL_DATA.BINARY_DATA IS 'Binary data storage\nSupports: images, documents, archives\tTab-separated info\r\nWindows line endings';
COMMENT ON COLUMN SPECIAL_DATA.CATEGORY IS 'Category field: use % for wildcard searches, _ for single char matching';
COMMENT ON COLUMN SPECIAL_DATA.STATUS IS 'Status field - values: ''ACTIVE'', ''INACTIVE'', ''PENDING''; DROP TABLE users; --';
COMMENT ON TABLE MULTILANG_CONTENT IS 'Multilingual content: English, Español, 中文, العربية, Русский, 日本語';
COMMENT ON COLUMN MULTILANG_CONTENT.TITLE_EN IS 'English title field';
COMMENT ON COLUMN MULTILANG_CONTENT.TITLE_ES IS 'Título en español';
COMMENT ON COLUMN MULTILANG_CONTENT.TITLE_ZH IS '中文标题字段 - 支持简体和繁体中文';
COMMENT ON COLUMN MULTILANG_CONTENT.CONTENT_TEXT IS 'Content field supporting:
1. HTML tags: <b>bold</b>, <i>italic</i>, <a href="http://example.com">links</a>
2. Markdown: **bold**, *italic*, [link](url), backtick-code-backtick
3. Special symbols: © ® ™ § ¶ † ‡ • … ‰ ′ ″
4. Emojis: 😀 😃 😄 😁 🚀 ⭐ 🎉 💯
5. Mathematical: ∀x∈ℝ: x² ≥ 0, ∑(i=1 to n) i = n(n+1)/2';
COMMENT ON COLUMN MULTILANG_CONTENT.TAGS IS 'XML tags: <tag type="category" value="news"/><tag type="priority" value="high"/>End of XML';
COMMENT ON TABLE COMPLEX_TYPES IS 'Advanced data types testing table
Supports: XMLType, Timestamps with timezone, Intervals, RAW data
Performance: Optimized for OLTP workloads @ 10K+ TPS
Security: Row-level security enabled, audit trails maintained';
COMMENT ON COLUMN COMPLEX_TYPES.XML_DATA IS 'XMLType column for structured XML storage
Example: <?xml version="1.0" encoding="UTF-8"?>
<root><item id="1" name="test">Content</item></root>';
COMMENT ON COLUMN COMPLEX_TYPES.TIMESTAMP_TZ IS 'Timestamp with timezone: YYYY-MM-DD HH24:MI:SS.FF TZR
Default: SYSTIMESTAMP (system timestamp with session timezone)';
COMMENT ON COLUMN COMPLEX_TYPES.INTERVAL_DS IS 'Interval Day to Second: +/-DD HH:MI:SS.FFFFFF
Range: +/-999999999 days, precision up to nanoseconds';
`,
			migrationDDL: `
-- Drop table with all its comments
DROP TABLE COMPLEX_TYPES CASCADE CONSTRAINTS;

-- Remove all comments from remaining tables
COMMENT ON COLUMN MULTILANG_CONTENT.TAGS IS '';
COMMENT ON COLUMN MULTILANG_CONTENT.CONTENT_TEXT IS '';
COMMENT ON COLUMN MULTILANG_CONTENT.TITLE_ZH IS '';
COMMENT ON COLUMN MULTILANG_CONTENT.TITLE_ES IS '';
COMMENT ON COLUMN MULTILANG_CONTENT.TITLE_EN IS '';
COMMENT ON TABLE MULTILANG_CONTENT IS '';

COMMENT ON COLUMN SPECIAL_DATA.STATUS IS '';
COMMENT ON COLUMN SPECIAL_DATA.CATEGORY IS '';
COMMENT ON COLUMN SPECIAL_DATA.BINARY_DATA IS '';
COMMENT ON COLUMN SPECIAL_DATA.UNICODE_TEXT IS '';
COMMENT ON COLUMN SPECIAL_DATA.METADATA_JSON IS '';
COMMENT ON COLUMN SPECIAL_DATA.DATA_VALUE IS '';
COMMENT ON TABLE SPECIAL_DATA IS '';
`,
			description: "Reverse: Remove all comments with special characters",
		},
	}

	for _, tc := range testCases {
		t.Run(tc.name, func(t *testing.T) {
			// Step 1: Initialize the database schema and get schema result A
			portInt, err := strconv.Atoi(port.Port())
			require.NoError(t, err)
			testDB, err := openTestDatabase(host, portInt, "testuser", "testpass")
			require.NoError(t, err)
			defer testDB.Close()

			// Set current schema to TESTUSER
			_, err = testDB.Exec("ALTER SESSION SET CURRENT_SCHEMA = TESTUSER")
			require.NoError(t, err)

			// Clean up any existing objects
			// Drop procedures
			_, _ = testDB.Exec("BEGIN FOR c IN (SELECT object_name FROM user_objects WHERE object_type = 'PROCEDURE') LOOP EXECUTE IMMEDIATE 'DROP PROCEDURE ' || c.object_name; END LOOP; END;")
			// Drop packages (must be before functions/procedures that might be in packages)
			_, _ = testDB.Exec("BEGIN FOR c IN (SELECT object_name FROM user_objects WHERE object_type = 'PACKAGE') LOOP EXECUTE IMMEDIATE 'DROP PACKAGE ' || c.object_name; END LOOP; END;")
			// Drop functions
			_, _ = testDB.Exec("BEGIN FOR c IN (SELECT object_name FROM user_objects WHERE object_type = 'FUNCTION') LOOP EXECUTE IMMEDIATE 'DROP FUNCTION ' || c.object_name; END LOOP; END;")
			// Drop types
			_, _ = testDB.Exec("BEGIN FOR c IN (SELECT type_name FROM user_types) LOOP EXECUTE IMMEDIATE 'DROP TYPE ' || c.type_name || ' FORCE'; END LOOP; END;")
			// Drop materialized views
			_, _ = testDB.Exec("BEGIN FOR c IN (SELECT mview_name FROM user_mviews) LOOP EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || c.mview_name; END LOOP; END;")
			// Drop views
			_, _ = testDB.Exec("BEGIN FOR c IN (SELECT view_name FROM user_views) LOOP EXECUTE IMMEDIATE 'DROP VIEW ' || c.view_name; END LOOP; END;")
			// Drop tables (with CASCADE CONSTRAINTS to handle foreign keys)
			_, _ = testDB.Exec("BEGIN FOR c IN (SELECT table_name FROM user_tables) LOOP EXECUTE IMMEDIATE 'DROP TABLE ' || c.table_name || ' CASCADE CONSTRAINTS'; END LOOP; END;")
			// Drop sequences (excluding system-generated ones)
			_, _ = testDB.Exec("BEGIN FOR c IN (SELECT sequence_name FROM user_sequences WHERE sequence_name NOT LIKE 'ISEQ$$_%') LOOP EXECUTE IMMEDIATE 'DROP SEQUENCE ' || c.sequence_name; END LOOP; END;")

			// Execute initial schema
			if err := executeStatements(testDB, tc.initialSchema); err != nil {
				t.Fatalf("Failed to execute initial schema: %v", err)
			}

			schemaA, err := getSyncMetadataForGenerateMigration(ctx, host, portInt)
			require.NoError(t, err)

			// Step 2: Do some migration and get schema result B
			if err := executeStatements(testDB, tc.migrationDDL); err != nil {
				t.Fatalf("Failed to execute migration DDL: %v", err)
			}

			schemaB, err := getSyncMetadataForGenerateMigration(ctx, host, portInt)
			require.NoError(t, err)

			// Step 3: Call generate migration to get the rollback DDL
			// Convert to model.DatabaseSchema
			dbMetadataA := model.NewDatabaseMetadata(schemaA, nil, nil, storepb.Engine_ORACLE, false)
			dbMetadataB := model.NewDatabaseMetadata(schemaB, nil, nil, storepb.Engine_ORACLE, false)

			// Get diff from B to A (to generate rollback)
			diff, err := schema.GetDatabaseSchemaDiff(storepb.Engine_ORACLE, dbMetadataB, dbMetadataA)
			require.NoError(t, err)

			// Generate rollback migration
			rollbackDDL, err := schema.GenerateMigration(storepb.Engine_ORACLE, diff)
			require.NoError(t, err)

			// Step 4: Run rollback DDL and get schema result C
			if err := executeStatements(testDB, rollbackDDL); err != nil {
				t.Fatalf("Failed to execute rollback DDL: %v", err)
			}

			schemaC, err := getSyncMetadataForGenerateMigration(ctx, host, portInt)
			require.NoError(t, err)

			// Step 5: Compare schema result A and C to ensure they are the same
			normalizeMetadataForComparison(schemaA)
			normalizeMetadataForComparison(schemaC)

			// Normalize column positions to 0 before comparison to ignore position differences
			normalizeColumnPositions(schemaA)
			normalizeColumnPositions(schemaC)

			// Use cmp with protocmp for proto message comparison
			if diff := cmp.Diff(schemaA, schemaC, protocmp.Transform()); diff != "" {
				t.Errorf("Schema mismatch after rollback (-want +got):\n%s", diff)
			}
		})
	}
}

// openTestDatabase opens a connection to the test Oracle database
func openTestDatabase(host string, port int, username, password string) (*sql.DB, error) {
	dsn := fmt.Sprintf("oracle://%s:%s@%s:%d/FREEPDB1", username, password, host, port)
	db, err := sql.Open("oracle", dsn)
	if err != nil {
		return nil, err
	}
	if err := db.Ping(); err != nil {
		return nil, err
	}
	return db, nil
}

// executeStatements executes multiple SQL statements, handling both regular DDL and PL/SQL blocks
func executeStatements(db *sql.DB, statements string) error {
	// Set current schema to TESTUSER before executing any statements
	if _, err := db.Exec("ALTER SESSION SET CURRENT_SCHEMA = TESTUSER"); err != nil {
		return errors.Wrapf(err, "failed to set current schema")
	}

	// Use plsql.SplitSQL to properly split Oracle SQL statements
	stmts, err := plsqlparser.SplitSQL(statements)
	if err != nil {
		return errors.Wrapf(err, "failed to split SQL statements")
	}

	// Execute each statement
	for _, singleSQL := range stmts {
		stmt := strings.TrimSpace(singleSQL.Text)
		if stmt == "" {
			continue
		}

		// Skip pure comment lines
		if strings.HasPrefix(stmt, "--") {
			continue
		}

		// Execute the statement
		if _, err := db.Exec(stmt); err != nil {
			// Handle Oracle-specific issues where materialized views are misclassified as tables
			if strings.Contains(err.Error(), "must use DROP MATERIALIZED VIEW") {
				// Try to fix the statement by replacing DROP TABLE with DROP MATERIALIZED VIEW
				if strings.HasPrefix(strings.ToUpper(stmt), "DROP TABLE") {
					fixedStmt := strings.Replace(stmt, "DROP TABLE", "DROP MATERIALIZED VIEW", 1)
					if _, retryErr := db.Exec(fixedStmt); retryErr == nil {
						continue // Successfully executed with corrected statement
					}
				}
			}
			// Handle system-generated virtual column indexes that cannot be manually created
			if strings.Contains(err.Error(), "invalid identifier") && strings.Contains(stmt, "SYS_NC") {
				// Skip statements that reference system-generated virtual columns
				continue
			}
			return errors.Wrapf(err, "failed to execute statement: %s", stmt)
		}
	}

	return nil
}

// getSyncMetadataForGenerateMigration retrieves metadata from the live database using Driver.SyncDBSchema
func getSyncMetadataForGenerateMigration(ctx context.Context, host string, port int) (*storepb.DatabaseSchemaMetadata, error) {
	// Create a driver instance using the oracle package
	driver := &oracledb.Driver{}

	// Create connection config
	config := db.ConnectionConfig{
		DataSource: &storepb.DataSource{
			Type:        storepb.DataSourceType_ADMIN,
			Username:    "testuser",
			Host:        host,
			Port:        fmt.Sprintf("%d", port),
			Database:    "",
			ServiceName: "FREEPDB1",
		},
		Password: "testpass",
		ConnectionContext: db.ConnectionContext{
			EngineVersion: "23.0",     // Oracle 23c Free
			DatabaseName:  "TESTUSER", // Oracle schema names are uppercase
		},
	}

	// Open connection using the driver
	openedDriver, err := driver.Open(ctx, storepb.Engine_ORACLE, config)
	if err != nil {
		return nil, err
	}
	defer openedDriver.Close(ctx)

	// Use SyncDBSchema to get the metadata
	oracleDriver, ok := openedDriver.(*oracledb.Driver)
	if !ok {
		return nil, errors.New("failed to cast to oracle.Driver")
	}

	metadata, err := oracleDriver.SyncDBSchema(ctx)
	if err != nil {
		return nil, err
	}

	return metadata, nil
}

// normalizeMetadataForComparison normalizes metadata to ignore differences that don't affect schema equality
func normalizeMetadataForComparison(metadata *storepb.DatabaseSchemaMetadata) {
	// Clear database name as it might differ
	metadata.Name = ""

	// Normalize schemas
	for _, schema := range metadata.Schemas {
		// Normalize tables
		for _, table := range schema.Tables {
			table.DataSize = 0
			table.IndexSize = 0
			table.RowCount = 0

			// Filter out system-generated indexes that reference virtual columns
			var filteredIndexes []*storepb.IndexMetadata
			for _, idx := range table.Indexes {
				// Skip indexes that reference system-generated virtual columns
				hasVirtualColumn := false
				for _, expr := range idx.Expressions {
					if strings.HasPrefix(expr, "SYS_NC") {
						hasVirtualColumn = true
						break
					}
				}
				if !hasVirtualColumn {
					filteredIndexes = append(filteredIndexes, idx)
				}
			}
			table.Indexes = filteredIndexes

			// Sort columns by name for consistent comparison
			sortColumnsByName(table.Columns)

			// Sort indexes by name
			sortIndexesByName(table.Indexes)

			// Sort foreign keys by name
			sortForeignKeysByName(table.ForeignKeys)

			// Sort check constraints by name
			sortCheckConstraintsByName(table.CheckConstraints)
		}

		// Filter out system-generated sequences
		var filteredSequences []*storepb.SequenceMetadata
		for _, seq := range schema.Sequences {
			if !strings.HasPrefix(seq.Name, "ISEQ$$_") {
				filteredSequences = append(filteredSequences, seq)
			}
		}
		schema.Sequences = filteredSequences

		// Sort all collections for consistent comparison
		sortTablesByName(schema.Tables)
		sortViewsByName(schema.Views)
		sortMaterializedViewsByName(schema.MaterializedViews)
		sortFunctionsByName(schema.Functions)
		sortSequencesByName(schema.Sequences)
	}

	// Sort schemas by name
	sortSchemasByName(metadata.Schemas)

	// Sort extensions by name
	sortExtensionsByName(metadata.Extensions)
}

// Sorting helper functions
func sortSchemasByName(schemas []*storepb.SchemaMetadata) {
	slices.SortFunc(schemas, func(a, b *storepb.SchemaMetadata) int {
		return strings.Compare(a.Name, b.Name)
	})
}

func sortTablesByName(tables []*storepb.TableMetadata) {
	slices.SortFunc(tables, func(a, b *storepb.TableMetadata) int {
		return strings.Compare(a.Name, b.Name)
	})
}

func sortColumnsByName(columns []*storepb.ColumnMetadata) {
	slices.SortFunc(columns, func(a, b *storepb.ColumnMetadata) int {
		return strings.Compare(a.Name, b.Name)
	})
}

func sortIndexesByName(indexes []*storepb.IndexMetadata) {
	slices.SortFunc(indexes, func(a, b *storepb.IndexMetadata) int {
		return strings.Compare(a.Name, b.Name)
	})
}

func sortForeignKeysByName(fks []*storepb.ForeignKeyMetadata) {
	slices.SortFunc(fks, func(a, b *storepb.ForeignKeyMetadata) int {
		return strings.Compare(a.Name, b.Name)
	})
}

func sortCheckConstraintsByName(checks []*storepb.CheckConstraintMetadata) {
	slices.SortFunc(checks, func(a, b *storepb.CheckConstraintMetadata) int {
		return strings.Compare(a.Name, b.Name)
	})
}

// normalizeColumnPositions sets all column positions to 0 to ignore position differences during comparison
func normalizeColumnPositions(metadata *storepb.DatabaseSchemaMetadata) {
	for _, schema := range metadata.Schemas {
		for _, table := range schema.Tables {
			for _, column := range table.Columns {
				column.Position = 0
			}
		}
	}
}

func sortViewsByName(views []*storepb.ViewMetadata) {
	slices.SortFunc(views, func(a, b *storepb.ViewMetadata) int {
		return strings.Compare(a.Name, b.Name)
	})
}

func sortMaterializedViewsByName(mvs []*storepb.MaterializedViewMetadata) {
	slices.SortFunc(mvs, func(a, b *storepb.MaterializedViewMetadata) int {
		return strings.Compare(a.Name, b.Name)
	})
}

func sortFunctionsByName(functions []*storepb.FunctionMetadata) {
	slices.SortFunc(functions, func(a, b *storepb.FunctionMetadata) int {
		return strings.Compare(a.Name, b.Name)
	})
}

func sortSequencesByName(sequences []*storepb.SequenceMetadata) {
	slices.SortFunc(sequences, func(a, b *storepb.SequenceMetadata) int {
		return strings.Compare(a.Name, b.Name)
	})
}

func sortExtensionsByName(extensions []*storepb.ExtensionMetadata) {
	slices.SortFunc(extensions, func(a, b *storepb.ExtensionMetadata) int {
		return strings.Compare(a.Name, b.Name)
	})
}
